﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:ADU_CONARR
	/// </summary>
	public partial class ADU_CONARR : BaseDAL<GSLMS.Entity.TB_ADU_CONARR>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public ADU_CONARR()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_ADU_CONARR model)
		{
			bool flag = false;
            //OracleParameter[] parms = new OracleParameter[]{
            //                    new OracleParameter("P_ID", model.ID),
            //                    new OracleParameter("P_PROID", model.PROID),
            //                    new OracleParameter("P_ARRDATE", model.ARRDATE),
            //                    new OracleParameter("P_ARRMEMBERS", model.ARRMEMBERS),
            //                    new OracleParameter("P_ARRMEMBERSID", model.ARRMEMBERSID),
            //                    new OracleParameter("P_HEADMAN", model.HEADMAN),
            //                    new OracleParameter("P_HEADMANID", model.HEADMANID),
            //                    new OracleParameter("P_ARRBY", model.ARRBY),
            //                    new OracleParameter("P_ARRBYID", model.ARRBYID),
            //                    new OracleParameter("P_CREATEDATE", model.CREATEDATE),
            //                    new OracleParameter("P_REMARK", model.REMARK),
            //                    new OracleParameter("P_STATUS", model.STATUS)};

            //for (int i = 0; i < 12; i++)
            //{
            //    parms[i].Direction = ParameterDirection.Input;
            //}

            //try
            //{
            //    OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_conarr_add", parms);
            //    flag = true;
            //}
            //catch (Exception ex)
            //{
            //    log.ExceptionHand(ex);
            //}
			return flag;
		}

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_ADU_CONARR model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID", model.PROID),
								new OracleParameter("P_ARRDATE", model.ARRDATE),
								new OracleParameter("P_ARRMEMBERS", model.ARRMEMBERS),
								new OracleParameter("P_ARRMEMBERSID", model.ARRMEMBERSID),
								new OracleParameter("P_HEADMAN", model.HEADMAN),
								new OracleParameter("P_HEADMANID", model.HEADMANID),
								new OracleParameter("P_ARRBY", model.ARRBY),
								new OracleParameter("P_ARRBYID", model.ARRBYID),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE),
								new OracleParameter("P_REMARK", model.REMARK),
								new OracleParameter("P_STATUS", model.STATUS)};
  
			for (int i = 0; i < 12; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_conarr_update", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}


        /// <summary>
        /// 审议安排退回
        /// </summary>
        /// <param name="id">安排记录ID</param>
        /// <param name="actionBy">操作人</param>
        /// <param name="backReason">退回原因</param>
        /// <param name="backtoFlow">退回到流程</param>
        /// <returns></returns>
        public int BackTo(long id,string actionBy,string backReason,int backtoFlow)
        {
            int num = 0;

            OracleParameter[] parms = new OracleParameter[]{
                                new OracleParameter("p_id", id),
                                new OracleParameter("p_actionby",actionBy),
                                new OracleParameter("P_BACKREASON",backReason),
                                new OracleParameter("returnvalue",OracleType.Int32),
                                new OracleParameter("p_backtoflow",backtoFlow)
            };

             
            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Input;
            parms[3].Direction = ParameterDirection.Output;
            parms[4].Direction = ParameterDirection.Input;


            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_conarr_backto", parms);
                if (parms[3].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[3].Value.ToString());
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return num;
        }

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
            //OracleParameter[] parms =new OracleParameter[]{
            //                    new OracleParameter("P_ID", ID)};
  
            //    parms[0].Direction = ParameterDirection.Input;

            //try
            //{
            //    OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_adu_conarr_delete", parms);
            //    flag = true;
            //}
            //catch (Exception ex)
            //{
            //    log.ExceptionHand(ex);
            //}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_ADU_CONARR DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_ADU_CONARR model = new GSLMS.Entity.TB_ADU_CONARR();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["PROID"]!=null && dr["PROID"].ToString()!="")
				{
					model.PROID=OracleHelper.ToLong(dr["PROID"].ToString());
				}
				if(dr["ARRDATE"]!=null && dr["ARRDATE"].ToString()!="")
				{
					model.ARRDATE=DateTime.Parse(dr["ARRDATE"].ToString());
				}
				if(dr["ARRMEMBERS"]!=null && dr["ARRMEMBERS"].ToString()!="")
				{
					model.ARRMEMBERS=dr["ARRMEMBERS"].ToString();
				}
				if(dr["ARRMEMBERSID"]!=null && dr["ARRMEMBERSID"].ToString()!="")
				{
					model.ARRMEMBERSID=dr["ARRMEMBERSID"].ToString();
				}
				if(dr["HEADMAN"]!=null && dr["HEADMAN"].ToString()!="")
				{
					model.HEADMAN=dr["HEADMAN"].ToString();
				}
				if(dr["HEADMANID"]!=null && dr["HEADMANID"].ToString()!="")
				{
					model.HEADMANID=OracleHelper.ToLong(dr["HEADMANID"].ToString());
				}
				if(dr["ARRBY"]!=null && dr["ARRBY"].ToString()!="")
				{
					model.ARRBY=dr["ARRBY"].ToString();
				}
				if(dr["ARRBYID"]!=null && dr["ARRBYID"].ToString()!="")
				{
					model.ARRBYID=OracleHelper.ToLong(dr["ARRBYID"].ToString());
				}
				if(dr["CREATEDATE"]!=null && dr["CREATEDATE"].ToString()!="")
				{
					model.CREATEDATE=DateTime.Parse(dr["CREATEDATE"].ToString());
				}
				if(dr["REMARK"]!=null && dr["REMARK"].ToString()!="")
				{
					model.REMARK=dr["REMARK"].ToString();
				}
				if(dr["STATUS"]!=null && dr["STATUS"].ToString()!="")
				{
                    model.STATUS = OracleHelper.ToInt(dr["STATUS"].ToString());
				}


               

                if (dr["CustName"] != null && dr["CustName"].ToString() != "")
                {
                    model.CustName = dr["CustName"].ToString();
                }

                if (dr["CustNo"] != null && dr["CustNo"].ToString() != "")
                {
                    model.CustNo = dr["CustNo"].ToString();
                }

                if (dr["SystemID"] != null && dr["SystemID"].ToString() != "")
                {
                    model.SystemID = OracleHelper.ToInt(dr["SystemID"].ToString());
                }

                if (dr["Amount"] != null && dr["Amount"].ToString() != "")
                {
                    model.Amount = OracleHelper.ToDecimal(dr["Amount"].ToString());
                }

                if (dr["BussType"] != null && dr["BussType"].ToString() != "")
                {
                    model.BussType = OracleHelper.ToInt(dr["BussType"].ToString());
                }

                if (dr["ApplyNo"] != null && dr["ApplyNo"].ToString() != "")
                {
                    model.ApplyNo = dr["ApplyNo"].ToString();
                }

                if (dr["ApplyDate"] != null && dr["ApplyDate"].ToString() != "")
                {
                    model.ApplyDate = DateTime.Parse(dr["ApplyDate"].ToString());
                }

                if (dr["SalesManID"] != null && dr["SalesManID"].ToString() != "")
                {
                    model.SalesManID = OracleHelper.ToLong(dr["SalesManID"].ToString());
                }

                if (dr["SalesMan"] != null && dr["SalesMan"].ToString() != "")
                {
                    model.SalesMan = dr["SalesMan"].ToString();
                }

                if (dr["ProductID"] != null && dr["ProductID"].ToString() != "")
                {
                    model.ProductID = OracleHelper.ToLong(dr["ProductID"].ToString());
                }

                if (dr["ProductName"] != null && dr["ProductName"].ToString() != "")
                {
                    model.ProductName = dr["ProductName"].ToString();
                }

                if (dr["DirectorAuditTime"] != null && dr["DirectorAuditTime"].ToString() != "")
                {
                    model.DirectorAuditTime = DateTime.Parse(dr["DirectorAuditTime"].ToString());
                }

                if (dr["RiskAuditBy"] != null && dr["RiskAuditBy"].ToString() != "")
                {
                    model.RiskAuditBy = dr["RiskAuditBy"].ToString();
                }

                if (dr["GENERALRATE"] != null && dr["GENERALRATE"].ToString() != "")
                {
                    model.GENERALRATE = OracleHelper.ToDecimal(dr["GENERALRATE"].ToString());
                }

                //ap.custname,ap.custno,ap.systemid,ap.amount,ap.busstype,ap.applyno,ap.appdate,
                //AP.SALESMANID,ap.salesman,ap.productid,ap.productname,
                //directoraudittime
                //riskauditby


				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_ADU_CONARR GetByID(long ID)
		{
			GSLMS.Entity.TB_ADU_CONARR sModel = new GSLMS.Entity.TB_ADU_CONARR();
			
            OracleParameter[] parms = new OracleParameter[] {
              new OracleParameter("p_id",ID),
              new OracleParameter("p_cur",OracleType.Cursor),
              new OracleParameter("p_ocur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;
            parms[2].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_conarr_get", parms);



			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
                if (ds.Tables.Count > 1&&ds.Tables[1].Rows.Count>0)
                    sModel.RiskDirectorAuditContent = ds.Tables[1].Rows[0]["riskdirectorcontent"].ToString();
			}
			else
			{
			return null;
			}
			return sModel;
		}



		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_ADU_CONARR> GetList(GSLMS.Entity.TB_ADU_CONARR searchParam)
		{
			var list=new List<GSLMS.Entity.TB_ADU_CONARR>();
			DataTable dt = null;
            string tblName = "v_adu_conarr";
			string sqlWhere="";
			string strSort="";
			int pageSize=15;
			int pageIndex=1;
            string strFields = "v_adu_conarr.*";
			int totalCount = 0;
			int countFlag = 1;

            //是否计算总数
            countFlag = searchParam.Pagination.ReCount? 1 : 0;
            pageSize = searchParam.Pagination.PageSize > 0 ? searchParam.Pagination.PageSize : 15;
            pageIndex = searchParam.Pagination.CurrentIndex > 0 ? searchParam.Pagination.CurrentIndex : 1;

            if (searchParam.CustName != null && searchParam.CustName.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CustName.Trim()) + "%'";
            }

            if (searchParam.SystemID > 0)
            {
                sqlWhere += " and systemid=" + searchParam.SystemID.ToString();
            }

            if (searchParam.DirectorAuditTime != null && searchParam.DirectorAuditTime.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime>=to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.DirectorAuditTime_End != null && searchParam.DirectorAuditTime_End.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime<to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime_End).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.STATUS == 1)
            {
                if (searchParam.STATUS2 == 0)
                    sqlWhere += " and STATUS<=0";
            }
            else
            { 
              if (searchParam.STATUS2 == 1)
                  sqlWhere += " and STATUS>0";
            }


			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }

			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_ADU_CONARR model = new GSLMS.Entity.TB_ADU_CONARR();
					model = DataRowToModel(dt.Rows[i]);

                    
					list.Add(model);
				}
			}
			else
			{
				return null;
			}
			return list;
		}


        /// <summary>
        /// 分页提取数据
        /// </summary>
        public List<GSLMS.Entity.TB_ADU_CONARR> GetConAduList(GSLMS.Entity.TB_ADU_CONARR searchParam)
        {
            var list = new List<GSLMS.Entity.TB_ADU_CONARR>();
            DataTable dt = null;
            string tblName = "v_adu_conarr";
            string sqlWhere = "";
            string strSort = "";
            int pageSize = 15;
            int pageIndex = 1;
            string strFields = "v_adu_conarr.*,(select count(1) from TB_ADU_CONADU where ARRID=v_adu_conarr.id and AUDITBY='" + searchParam.ConAduBy + "') conaduflag";
            int totalCount = 0;
            int countFlag = 1;

            //是否计算总数
            countFlag = searchParam.Pagination.ReCount ? 1 : 0;
            pageSize = searchParam.Pagination.PageSize > 0 ? searchParam.Pagination.PageSize : 15;
            pageIndex = searchParam.Pagination.CurrentIndex > 0 ? searchParam.Pagination.CurrentIndex : 1;

            if (searchParam.CustName != null && searchParam.CustName.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CustName.Trim()) + "%'";
            }

            if (searchParam.SystemID > 0)
            {
                sqlWhere += " and systemid=" + searchParam.SystemID.ToString();
            }

            if (searchParam.DirectorAuditTime != null && searchParam.DirectorAuditTime.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime>=to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.DirectorAuditTime_End != null && searchParam.DirectorAuditTime_End.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime<to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime_End).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            //已安排
            sqlWhere += " and STATUS=1";

            if (searchParam.ConAduID>0)
            {
                sqlWhere += " and instr(','||ARRMEMBERSID||',',','||to_char("+searchParam.ConAduID.ToString()+")||',',1,1)>0";
            }

            //使用为审批
            if (searchParam.STATUS == 1)
            {
                //待审批
                if (searchParam.STATUS2 == 0)
                    sqlWhere += " and pkg_audit.f_conadu_chk(ID,'"+searchParam.ConAduBy+"')=0";
            }
            else
            {
                //已审批
                if (searchParam.STATUS2 == 1)
                    sqlWhere += " and pkg_audit.f_conadu_chk(ID,'" + searchParam.ConAduBy + "')=1";
            }


            dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex, countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }

            
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_ADU_CONARR model = new GSLMS.Entity.TB_ADU_CONARR();
                    model = DataRowToModel(dt.Rows[i]);

                    model.ConAduFlag = Convert.ToInt16(dt.Rows[i]["conaduflag"].ToString());
                    list.Add(model);
                }
            }
            else
            {
                return null;
            }
            return list;
        }

        /// <summary>
        /// 分页提取数据
        /// </summary>
        public List<GSLMS.Entity.TB_ADU_CONARR> GetConSumList(GSLMS.Entity.TB_ADU_CONARR searchParam)
        {
            var list = new List<GSLMS.Entity.TB_ADU_CONARR>();
            DataTable dt = null;
            string tblName = "v_adu_conarr";
            string sqlWhere = "";
            string strSort = "";
            int pageSize = 15;
            int pageIndex = 1;
            string strFields = "v_adu_conarr.*,(select count(1) from TB_ADU_CONSUM where ARRID=v_adu_conarr.id) consumflag";
            int totalCount = 0;
            int countFlag = 1;

            //是否计算总数
            countFlag = searchParam.Pagination.ReCount ? 1 : 0;
            pageSize = searchParam.Pagination.PageSize > 0 ? searchParam.Pagination.PageSize : 15;
            pageIndex = searchParam.Pagination.CurrentIndex > 0 ? searchParam.Pagination.CurrentIndex : 1;

            if (searchParam.CustName != null && searchParam.CustName.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CustName.Trim()) + "%'";
            }

            if (searchParam.SystemID > 0)
            {
                sqlWhere += " and systemid=" + searchParam.SystemID.ToString();
            }

            if (searchParam.DirectorAuditTime != null && searchParam.DirectorAuditTime.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime>=to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.DirectorAuditTime_End != null && searchParam.DirectorAuditTime_End.ToString().Trim() != "")
            {
                sqlWhere += " and DirectorAuditTime<to_date('" + Convert.ToDateTime(searchParam.DirectorAuditTime_End).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            //已安排
            sqlWhere += " and STATUS=1";

           

            //使用为审批
            if (searchParam.STATUS == 1)
            {
                //待汇总
                if (searchParam.STATUS2 == 0)
                    sqlWhere += " and pkg_audit.f_consum_chk(ID)=0";
            }
            else
            {
                //已汇总
                if (searchParam.STATUS2 == 1)
                    sqlWhere += " and pkg_audit.f_consum_chk(ID)=1";
            }


            dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex, countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }


            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_ADU_CONARR model = new GSLMS.Entity.TB_ADU_CONARR();
                    model = DataRowToModel(dt.Rows[i]);

                    model.ConSumflag = Convert.ToInt16(dt.Rows[i]["consumflag"].ToString());
                    list.Add(model);
                }
            }
            else
            {
                return null;
            }
            return list;
        }

		#endregion  Method
	}
}

